package com.business.music;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.beans.music.MusicInfo;
import com.business.log.LogListBus;
import com.sql.DBFactory;

public class MusicListBus {
	private Connection conn = null;

	private Statement stmt = null;

	private ResultSet rs = null;

	private static final Log log = LogFactory.getLog(LogListBus.class);

	@SuppressWarnings("unchecked")
	public List getAllMusicByUserName(String username) {
		List list = new ArrayList();
		String sql = "select * from music_info where   username='" + username
				+ "' order by music_no desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				MusicInfo mi = new MusicInfo();
				mi.setMusicName(rs.getString("music_name"));
				mi.setMusicMemo(rs.getString("music_memo"));
				mi.setMusicUrl(rs.getString("music_url"));
				mi.setMusictypeId(rs.getString("musictypeid"));
				mi.setMusicNo(rs.getString("music_no"));
				mi.setViewCount(rs.getString("view_count"));
				mi.setIsShow(rs.getString("isShow"));
				mi.setAddTime(rs.getString("add_time"));
				mi.setUsername(rs.getString("username"));
				mi.setClassName(rs.getString("class_name"));
				mi.setSinger(rs.getString("singer"));
				mi.setLrcUrl(rs.getString("lrc_url"));
				list.add(mi);
			}
		} catch (Exception e) {
			log.error("the select musicinfo exception:" + e.getMessage());
			log.error("the select musicinfo sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}
	@SuppressWarnings("unchecked")
	public List getAllMusicByShow(String username) {
		List list = new ArrayList();
		String sql = "select * from music_info where   username='" + username
				+ "' and isShow=1 order by music_no desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				MusicInfo mi = new MusicInfo();
				mi.setMusicName(rs.getString("music_name"));
				mi.setMusicMemo(rs.getString("music_memo"));
				mi.setMusicUrl(rs.getString("music_url"));
				mi.setMusictypeId(rs.getString("musictypeid"));
				mi.setMusicNo(rs.getString("music_no"));
				mi.setViewCount(rs.getString("view_count"));
				mi.setIsShow(rs.getString("isShow"));
				mi.setAddTime(rs.getString("add_time"));
				mi.setUsername(rs.getString("username"));
				mi.setClassName(rs.getString("class_name"));
				mi.setSinger(rs.getString("singer"));
				mi.setLrcUrl(rs.getString("lrc_url"));
				list.add(mi);
			}
		} catch (Exception e) {
			log.error("the select musicinfo exception:" + e.getMessage());
			log.error("the select musicinfo sql is: " + sql);
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}
	@SuppressWarnings("unchecked")
	public List getAllMusicByTypeID(int musictypeid) {
		List list = new ArrayList();
		String sql = "select * from music_info where musictypeid="
				+ musictypeid + " and isShow=1 order by add_time desc";
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				MusicInfo mi = new MusicInfo();
				mi.setMusicName(rs.getString("music_name"));
				mi.setMusicMemo(rs.getString("music_memo"));
				mi.setMusicUrl(rs.getString("music_url"));
				mi.setMusictypeId(rs.getString("musictypeid"));
				mi.setMusicNo(rs.getString("music_no"));
				mi.setViewCount(rs.getString("view_count"));
				mi.setIsShow(rs.getString("isShow"));
				mi.setAddTime(rs.getString("add_time"));
				mi.setUsername(rs.getString("username"));
				mi.setClassName(rs.getString("class_name"));
				mi.setSinger(rs.getString("singer"));
				mi.setLrcUrl(rs.getString("lrc_url"));
				list.add(mi);

			}
		} catch (Exception e) {
			log.error("the select musicinfo exception:" + e.getMessage());
			log.error("the select musicinfo sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return list;
	}

	public MusicInfo getMusicByNo(String musicNo) {
		MusicInfo mi = new MusicInfo();
		String sql = "select * from music_info where music_no='"
			+ musicNo + "'";
	try {
		conn = DBFactory.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		while (rs.next()) {
			mi.setMusicName(rs.getString("music_name"));
			mi.setMusicMemo(rs.getString("music_memo"));
			mi.setMusicUrl(rs.getString("music_url"));
			mi.setMusictypeId(rs.getString("musictypeid"));
			mi.setMusicNo(rs.getString("music_no"));
			mi.setViewCount(rs.getString("view_count"));
			mi.setIsShow(rs.getString("isShow"));
			mi.setAddTime(rs.getString("add_time"));
			mi.setUsername(rs.getString("username"));
			mi.setClassName(rs.getString("class_name"));
			mi.setSinger(rs.getString("singer"));
			mi.setLrcUrl(rs.getString("lrc_url"));
			}
		} catch (Exception e) {
			log.error("the select musicinfo exception:" + e.getMessage());
			log.error("the select musicinfo sql is: " + sql);
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException sqle) {
				log.error(sqle.toString());
			}
		}
		return mi;
	}

}
